-- phpMyAdmin SQL Dump
-- version 3.5.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 12. Mrz 2013 um 05:16
-- Server Version: 5.1.49
-- PHP-Version: 5.3.21

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Datenbank: `tippspiel`
--

DELIMITER $$
--
-- Funktionen
--
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_validate_user`(`user_name` VARCHAR(45), `pwd_md5` VARCHAR(45)) RETURNS tinyint(1)
    READS SQL DATA
    COMMENT 'check pwd'
RETURN (pwd_md5 = (SELECT `password` FROM `tb_user` WHERE `username` = user_name))$$

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_validate_user_by_id`(`user_id` INT(11), `pwd_md5` VARCHAR(45)) RETURNS tinyint(1)
    READS SQL DATA
    COMMENT 'check pwd'
RETURN (pwd_md5 = (SELECT `password` FROM `tb_user` WHERE `id` = user_id))$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `tb_points`
--

CREATE TABLE IF NOT EXISTS `tb_points` (
  `idcredits` int(11) NOT NULL AUTO_INCREMENT,
  `idtipp` int(11) NOT NULL,
  `addpoints` int(11) DEFAULT NULL,
  PRIMARY KEY (`idcredits`),
  UNIQUE KEY `idtipp` (`idtipp`),
  KEY `fk_tb_credits_tb_tipp1_idx` (`idtipp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `tb_resultate`
--

CREATE TABLE IF NOT EXISTS `tb_resultate` (
  `idResultate` int(11) NOT NULL AUTO_INCREMENT,
  `idspiel` int(11) NOT NULL,
  `resultat1` decimal(10,0) NOT NULL,
  `resultat2` decimal(10,0) NOT NULL,
  PRIMARY KEY (`idResultate`),
  KEY `fk_tb_resultate_tb_spiele1_idx` (`idspiel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `tb_spiele`
--

CREATE TABLE IF NOT EXISTS `tb_spiele` (
  `idspiel` int(11) NOT NULL AUTO_INCREMENT,
  `idteam1` int(11) NOT NULL,
  `idteam2` int(11) NOT NULL,
  `datum` date NOT NULL,
  PRIMARY KEY (`idspiel`),
  KEY `fk_tb_spiele_tb_teams1_idx` (`idteam1`),
  KEY `fk_tb_spiele_tb_teams2_idx` (`idteam2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `tb_teams`
--

CREATE TABLE IF NOT EXISTS `tb_teams` (
  `idteam` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`idteam`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `tb_tipp`
--

CREATE TABLE IF NOT EXISTS `tb_tipp` (
  `idtipp` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `idspiel` int(11) NOT NULL,
  `result1` decimal(10,0) NOT NULL,
  `result2` decimal(10,0) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idtipp`),
  KEY `fk_tb_tipp_tb_user_idx` (`userid`),
  KEY `fk_tb_tipp_tb_spiele1_idx` (`idspiel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `tb_user`
--

CREATE TABLE IF NOT EXISTS `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `forname` varchar(45) DEFAULT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  `email` varchar(60) DEFAULT NULL,
  `credits` int(11) DEFAULT NULL COMMENT 'Total (incremet only)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `username_UNIQUE` (`username`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `username_2` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Daten für Tabelle `tb_user`
--

INSERT INTO `tb_user` (`id`, `username`, `password`, `forname`, `lastname`, `email`, `credits`) VALUES
(2, 'test', '098f6bcd4621d373cade4e832627b4f6', NULL, NULL, NULL, NULL);

--
-- Constraints der exportierten Tabellen
--

--
-- Constraints der Tabelle `tb_points`
--
ALTER TABLE `tb_points`
  ADD CONSTRAINT `tb_points_ibfk_1` FOREIGN KEY (`idtipp`) REFERENCES `tb_tipp` (`idtipp`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints der Tabelle `tb_resultate`
--
ALTER TABLE `tb_resultate`
  ADD CONSTRAINT `fk_tb_resultate_tb_spiele1` FOREIGN KEY (`idspiel`) REFERENCES `tb_spiele` (`idspiel`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints der Tabelle `tb_spiele`
--
ALTER TABLE `tb_spiele`
  ADD CONSTRAINT `fk_tb_spiele_tb_teams1` FOREIGN KEY (`idteam1`) REFERENCES `tb_teams` (`idteam`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_tb_spiele_tb_teams2` FOREIGN KEY (`idteam2`) REFERENCES `tb_teams` (`idteam`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints der Tabelle `tb_tipp`
--
ALTER TABLE `tb_tipp`
  ADD CONSTRAINT `fk_tb_tipp_tb_user` FOREIGN KEY (`userid`) REFERENCES `tb_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_tb_tipp_tb_spiele1` FOREIGN KEY (`idspiel`) REFERENCES `tb_spiele` (`idspiel`) ON DELETE NO ACTION ON UPDATE NO ACTION;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
